#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   c2b_config_utils.py    
@Contact :   pengwei.sun@aihuishou.com
@License :   (C)Copyright aihuishou

@Modify Time      @Author       @Version    @Desciption
------------      -----------   --------    -----------
2022-10-19 11:34   pengwei.sun      1.0         None
'''

from src.utils.db_processor import presto_processor


# andoriod_product_ids=(27253,29261,28495,32588,29260,27550,36246,27860,31008,26464,35547,29637,27781,34575,30173,26912,32955,34053,35762,36744,27280,27512,34286,26613,
# 29443,32089,26536,27344,37376,34754,23756,25820,34576,27600,25806,23640,34756,35884,26707,25170,23819,26428,26102,20122,33416,24799,28968,47987,28168,29117,23668,37676,27980,
# 32890,34948,32066,26078,39142,30175,27301,23788,29693,32835,34576,25731,26165,25240,29118,32827,24347,24992,38600,23049,27627,28614,26431,28829,32068,25950,23866,26959,28612,
# 27764,29263,26492,34755,37519,26671,33009,37205,23862,27036,36493,19664,29291,34164,37627,34376,36510,26491,38564,25998,17895,32461,24240,29348,32244,29222,29246,26467,37066,
# 34753,34808,37677,38257,28480,37330,26422,38660,24348,28760,32460,26099,29115,25519,27859,23125,26513,27602,36804,38676,39087,35100,23614,36610,35164,52379,32581,26958,43349,
# 57425,36633,34686,33468,34574,36249,24217,32850,36628,29116,27549,33280,27822,34809,33415,35290,24239,35585,28121,31445,30044,35963,25676,58583,42861,62758,35564,35271,25232,
# 26170,28999,27877,34590,35349,76793,33281,26512,36784,32019,20692,26913,69469,36985,36805,35584,27859,27110,34504,29705,25097,32460,58585,23906,38584,26476,29836,28502,22706,
# 27861,26662,58309,35623,26808,55383,33436,26493,25677,36749,35830,27782,26911,35809,34324,22440,27035,84630,35290,25432,35006,29023,57426,36708,23587,39141,34086,42701,33498,
# 76595,21769,26167,27551,52548,27170,37051,34687,35044,29607,37018,27893,34097,43692,24552,34748,33007,31097,25765,54816,37487,39143,23891,39001,36095,34598,20421,23797,36394,
# 27643,39165,90726,37687,81750,26809,25948,26129,29777,27541,17887,29845,38696,35008,25401,26927,38297,37664,37094,15080,27297,38959,36250,37600,32022,27908,37535,37589,27782,
# 31007,69471,37019,35163,27646,29666,35322,43694,26197,42705,57423,42879,62752,25303,36291,31945,23841,28779,43693,32776,37077,32884,29000,30385,38884,29937,34054,25422,19665,
# 25239,34096,43303,69129,59536,34448,26490,68768,20210,34397,32716,37377,23619,34708,34734,36462,23077,52555,19534,30174,29807,32528,34709,29002,52551,32472,22441,28525,35527,
# 37680,17616,35197,24390,35005,24966,37255,58587,37604,34401,32988,23534,30384,43220,27334,59538,96407,25231,34795,38329,20658,27882,26783,23968,36939,58169,36687,36093,25682,
# 37650,20742,59956,72934,23375,25717,96320,27793,59539,32972,32632,34733,14990,17698,17259,68143,42860,63441,17967,25616,32958,29534,76608,54482,76437,25985,42880,25040,36248,
# 35106,33194,28443,75524,55534,57879,30423,31440,99441,43241,32055,68759,33425,90534,27190,34403,23535,43695,35451,35833,42842,62098,27270,37656,34431,30602,35181,43409,42519,
# 17896,27011,29706,36687,34287,27783,96404,26381,25400,36789,17527,50183,27006,26062,37679,43614,17912,34365,32821,42595,36248,23820,37578,26027,52380,68782,81493,99452,34402,
# 37596,82104,42610,25641,90758,29500,36352,23865,17869,26380,20173,17857,36897,34526,56588,64653,35941,37524,33363,90590,27756,25853,17488,42881,36692,39043,38961,76354,34405,
# 94077,26001,34730,81879,26098,38745,54809,35106,76882,32634,34443,81856,36908,32511,29366,27783,37109,37378,21036,59959,38250,43408,28466,84648,27405,17425,57619,76605,35699,
# 26535,34688,35660,39236,20499,32872,27784,34897,37703,43407,33012,58631,20751,66108,27988,78504,34896,35582,99440,39320,25273,25681,68391,94085,31767,97279,42596,83906,36160,
# 29805,52549,23286,34464,36398,29135,38630,20204,62755,58513,29606,47922,28478,31550,35671,36677,84715,34005,27491,37512,55748,33012,29413,23814,92590,96157,36300,52557,35557,
# 97281,42958,39177,68750,25935,81871,22187,50185,27483,17320,18030,26983,38882,36096,87961,23760,53126,74559,60629,57512,77316,35007,37217,27693,17988,35362,58251,36494,38619,
# 55464,43650,32243,75982,52550,43615,32974,69298,43696,24996,91803,17657,27997,17489,38394,27778,17493,39064,35700,52552,37606,36313,14972,20174,35795,39004,39020,59145,72658,
# 17746,17795,27950,20144,69616,36439,23776,89018,32006,27784,95936,21581,43664,28684,69834,37598,35596,57844,34468,42609,52320,20751,17793,50184,29208,26780,22709,48162,74097,
# 35125,43397,91768,37634,38664,35671,65095,23749,38972,37581,19533,21055,35256,27042,65768,38856,35591,24995,17713,29001,26201,32885,27372,28165,32054,59068,37508,30685,37283,
# 17889,32047,21885,39304,62829,43569,58152,82113,17486,23698,84300,43240,65453,68127,19657,17615,76598,92705,42676,37334,36247,26540,26545,17494,23857,25371,35622,17886,17521,
# 25521,37557,10025,38255,35453,20606,34694,32633,29439,24078,35035,23346,24497,87095,87588,29003,26769,79977,43360,29458,34352,28566,37100,36101,43393,23289,43056,20127,29519,
# 35515,29512,35125,17903,42646,26777,69768,23257,17579,17712,98349,35548,25777,17304,38359,58300,94040,39289,17614,52406,92439,17647,76513,17258,31353,97529,32837,28421,17611,
# 76903,33500,37528,81728,34949,17507,100642,17520,17549,25238,26168,30985,26545,30202,37653,17325,80724,17686,35497,20212,35135,26086,23460,29065,27501,27931,48128,34356,63537,
# 24993,91955,17669,65464,27991,28255,74184,35049,77595,34771,26268,26715,37545,33004,73334,22140,25778,39065,34044,37100,24793,98348,32471,24349,35480,42780,39029,36101,22117,
# 29556,74807,23764,29815,74448,17955,22115,25752,23758,24341,39214,27148,61768,36384,23281,43264,17785,59144,17321,20092,24215,73875,17345,25522,84662,95833,20698,43335,94171,
# 25431,34202,28253,17610,25776,32385,27535,58472,23275,37587,79871,27318,76898,27995,39021,66071,96533,25997,59542,26063,26266,25024,17337)

andoriod_product_ids =(17520,17521,17527,17549,17579,17610,17611,17614,17615,17616,17647,17657,17669,17686,17698,17712,17713,29260,34575,17746,36246,37376,17785,17793,17795,17857,17869,17886,17887,17889,17895,17896,17903,17912,17955,17967,
    17988,34754,18030,34576,39142,17726,19657,19664,19665,17752,20092,20122,20127,20144,20173,20174,20204,20210,20212,20421,20499,20606,25680,47987,20692,20698,20742,20751,21036,21055,21581,21769,21885,22115,22117,22140,
    22187,22440,22441,22706,34164,23049,23077,23125,23257,23275,23281,36510,23289,23346,23375,32955,36045,23460,23534,37676,23587,23614,23619,23640,23668,23698,37677,37066,23758,23760,23764,23422,23788,23797,23814,23819,34948,
    23841,23857,38257,23862,23865,23866,25679,27637,23968,36046,24078,36047,24217,35884,24240,24341,37519,27859,24349,24390,55383,29261,24497,24552,24793,32066,27782,24992,24993,24995,24996,25024,43510,28495,27550,25231,35547,
    25238,25239,26912,35762,26707,25371,26102,25400,25401,32835,25422,25431,25432,25519,25521,32827,25616,25641,28614,32068,37205,35585,27639,25682,25717,27640,38660,25752,25765,36628,25776,25777,25778,32588,34286,25820,33416,
    34574,36805,34756,28968,31008,32089,25948,38564,25985,36633,33280,37589,36248,26062,28612,32291,34753,26086,26098,26099,39001,26129,26165,26167,26168,26170,26197,35163,26266,38584,39165,27253,26422,29637,30173,34053,27280,
    26536,27344,26490,26491,28168,26493,29117,26513,26078,28829,32244,27822,57426,34748,26662,32292,27512,26431,29246,35830,34755,26777,26780,57425,34686,26808,34809,36687,33415,26911,34504,26464,26927,23756,29693,58583,26613,
    27860,32019,37018,36708,43692,36044,36493,27035,27036,31445,52551,35181,20079,32461,27781,27600,24799,32890,30175,25731,38600,33009,26467,32460,38676,36610,35290,30044,26913,33498,43693,27627,34808,29116,27541,36784,36394,
    34701,54816,34376,25998,29222,37627,34324,25240,27764,29291,26958,36249,32850,35271,34687,39143,37019,36291,38884,43511,36804,35584,27980,33468,33281,36985,24347,32776,43513,35100,29443,25806,25170,26428,26492,29115,27931,
    25676,34590,25097,27643,29777,27995,39087,29118,27893,43694,28999,34097,24348,24239,35527,43349,28253,34709,27301,34734,76793,28443,28466,29000,37377,32528,27110,37077,42879,26959,28121,35008,43695,28684,37330,28760,28779,
    39141,28480,27793,32958,42861,42705,27602,37255,35106,29003,31007,52555,23535,35809,27646,35164,52379,25677,27551,31945,29705,29023,29208,38696,36250,30384,20658,35623,43220,36789,29348,25950,29263,23906,42519,26512,42701,
    37600,33012,27861,32581,25232,58585,52548,37487,19534,29556,37604,43512,84630,37687,42860,55534,27784,35006,35322,36462,26671,33007,29706,37680,29836,32632,34526,35963,35349,28502,35044,32988,36095,35564,34086,27297,58169,
    32972,34897,34688,23891,32022,27882,30602,36749,34598,29845,30174,30985,34795,37051,30385,42881,26809,59539,29607,34708,29002,36939,37656,34096,35451,62098,27006,35671,37596,27877,31097,29666,69469,37664,34443,26476,28525,
    34405,42880,54809,33436,37535,43303,27190,38297,34397,58587,37524,34733,33194,26535,34896,25303,37094,69129,57879,43241,34402,37650,35005,38329,37578,32634,34464,27170,27270,38959,26783,36692,34365,27549,72934,29534,54482,
    27011,34730,27334,31440,68768,27783,37378,39043,52549,42596,34054,32716,34287,76354,62758,58309,69471,35582,37606,34448,29937,35833,25935,43409,81493,56588,38961,58251,26027,32511,76595,34431,82104,32884,34401,35660,39320,
    42676,36897,32872,27908,27756,36160,38630,81750,32472,35197,59956,63441,37679,90758,38745,37217,57512,68391,31767,37512,52380,33363,39177,25853,55464,68782,37703,35125,26381,42610,35699,29807,28478,27693,34403,32821,31550,
    36093,68143,36908,87961,66428,59536,59538,42595,43664,37100,36352,43408,35362,26001,83906,38619,52552,52320,43614,39004,35256,24966,26983,36398,39064,98528,39236,35007,36494,29512,84715,35035,38882,30423,25273,29805,23286,
    43650,50183,37109,36677,92590,27483,53126,81879,98525,57619,29606,32055,58631,23820,29366,35941,76605,33425,43569,43696,43615,27405,36096,23776,58300,9852,99442,

    10025,14972,14990,15080,17258,17259,36744,17304,17320,17321,17325,17337,17345,17425,17486,17488,17489,17493,17494,17507,17520,17521,17527,17549,17579,17610,17611,17614,17615,17616,17647,17657,17669,17686,17698,17712,17713,29260,34575,17746,36246,37376,17785,17793,17795,17857,17869,17886,17887,17889,17895,17896,17903,17912,17955,17967,17988,34754,18030,34576,39142,19657,19664,19665,20092,20122,20127,20144,20173,20174,20204,20210,20212,20421,20499,20606,47987,20692,20698,20742,20751,21036,21055,21581,21769,21885,22115,22117,22140,22187,22440,22441,22706,34164,23049,23077,23125,23257,23275,23281,36510,23289,23346,23375,32955,23460,23534,37676,23587,23614,23619,23640,23668,23698,37677,37066,23758,23760,23764,23788,23797,23814,23819,34948,23841,23857,38257,23862,23865,23866,23968,24078,24217,35884,24240,24341,37519,27859,24349,24390,55383,29261,24497,24552,24793,32066,27782,24992,24993,24995,24996,25024,28495,27550,25231,35547,25238,25239,26912,35762,26707,25371,26102,25400,25401,32835,25422,25431,25432,25519,25521,32827,25616,25641,28614,32068,37205,35585,25682,25717,38660,25752,25765,36628,25776,25777,25778,32588,34286,25820,33416,34574,36805,34756,28968,31008,32089,25948,38564,25985,36633,33280,37589,36248,26062,28612,34753,26086,26098,26099,39001,26129,26165,26167,26168,26170,26197,35163,26266,38584,39165,27253,26422,29637,30173,34053,27280,26536,27344,26490,26491,28168,26493,29117,26513,26078,28829,32244,27822,57426,34748,26662,27512,26431,29246,35830,34755,26777,26780,57425,34686,26808,34809,36687,33415,26911,34504,26464,26927,23756,29693,58583,26613,27860,32019,37018,36708,43692,36493,27035,27036,31445,52551,35181,32461,27781,27600,24799,32890,30175,25731,38600,33009,26467,32460,38676,36610,35290,30044,26913,33498,43693,27627,34808,29116,27541,36784,36394,54816,34376,25998,29222,37627,34324,25240,27764,29291,26958,36249,32850,35271,34687,39143,37019,36291,38884,36804,35584,27980,33468,33281,36985,24347,32776,35100,29443,25806,25170,26428,26492,29115,27931,25676,34590,25097,27643,29777,27995,39087,29118,27893,43694,28999,34097,24348,24239,35527,43349,28253,34709,27301,34734,76793,28443,28466,29000,37377,32528,27110,37077,42879,26959,28121,35008,43695,28684,37330,28760,28779,39141,28480,27793,32958,42861,42705,27602,37255,35106,29003,31007,52555,23535,35809,27646,35164,52379,25677,27551,31945,29705,29023,29208,38696,36250,30384,20658,35623,43220,36789,29348,25950,29263,23906,42519,26512,42701,37600,33012,27861,32581,25232,58585,52548,37487,19534,29556,37604,84630,37687,42860,55534,27784,35006,35322,36462,26671,33007,29706,37680,29836,32632,34526,35963,35349,28502,35044,32988,36095,35564,34086,27297,58169,32972,34897,34688,23891,32022,27882,30602,36749,34598,29845,30174,30985,34795,37051,30385,42881,26809,59539,29607,34708,29002,36939,37656,34096,35451,62098,27006,35671,37596,27877,31097,29666,69469,37664,34443,26476,28525,34405,42880,54809,33436,37535,43303,27190,38297,34397,58587,37524,34733,33194,26535,34896,25303,37094,69129,57879,43241,34402,37650,35005,38329,37578,32634,34464,27170,27270,38959,26783,36692,34365,27549,72934,29534,54482,27011,34730,27334,31440,68768,27783,37378,39043,52549,42596,34054,32716,34287,76354,62758,58309,69471,35582,37606,34448,29937,35833,25935,43409,81493,56588,38961,58251,26027,32511,76595,34431,82104,32884,34401,35660,39320,42676,36897,32872,27908,27756,36160,38630,81750,32472,35197,59956,63441,37679,90758,38745,37217,57512,68391,31767,37512,52380,33363,39177,25853,55464,68782,37703,35125,26381,42610,35699,29807,28478,27693,34403,32821,31550,36093,68143,36908,87961,59536,59538,42595,43664,37100,36352,43408,35362,26001,83906,38619,52552,52320,43614,39004,35256,24966,26983,36398,39064,39236,35007,36494,29512,84715,35035,38882,30423,25273,29805,23286,43650,50183,37109,36677,92590,27483,53126,81879,57619,29606,32055,58631,23820,29366,35941,76605,33425,43569,43696,43615,27405,36096,23776,58300,76608,75524,30685,43407,78504,60629,27950,19533,59959,38250,57423,66108,36247,90726,42842,84648,81856,76513,29500,29135,55748,32243,52550,36313,39020,38255,47922,35795,43393,39289,37598,35557,68750,
    50185,29439,32633,25997,57844,27778,29413,35591,27997,35700,23749,43056,37634,34005,52557,32047,35596,38856,62829,26540,36101,58513,26268,96407,68759,42958,58152,89018,50184,65095,32006,39304,27988,27491,48128,22709,29001,28165,38359,62752,76437,90534,27372,32054,87588,29065,35497,77316,38664,65768,37334,35135,90590,37508,99452,76882,69616,74097,43240,26715,37545,48162,34356,91803,26545,96404,38394,69768,76903,32385,26380,27501,61768,25522,33500,26769,29519,43397,64653,72658,43335,34949,36439,65464,43264,37283,59068,82113,37557,35515,39065,96157,69834,94085,81871,35622,35453,59145,35548,63537,99441,42609,29458,29815,96320,69298,62755,32885,38972,97279,74559,35480,65453,30202,58472,37587,94077,95936,52406,59144,28421,25681,27991,76598,81728,73875,39021,34468,32837,27318,75982,68127,92705,87095,100642,99440,25040,84662,28566,74448,24215,97281,34694,94040,77595,34771,74807,66071,39214,28255,37528,97529,34202,91768,31353,96533,84300,92439,39029,
    98348,26201,98349,36384,37653,74184,94171,32471,33004,43360,59542,91955,95833,37581,76898,27535,34352,42646,34044,73334,99442,

    111413,37330,111365,115120,114989,112846,113325,110293,112285,112648,111336,109022,110593,110295,110288,116018,115792,115287,112856,115658,110125,
    110590,112387,115118,115816,115507,109096,113518,115508,106485,109002,115818,99442,112897,110289,109352,110169,111445,112893,108928,115648,109424,
    110840,115119,109025,113308,115819,79977,111419,43697,107954,113608,36300,112919,112858,115793,107453,109422,116430,110838,98584,116105,109937,115766,
    80724,113488,110294,114969,112694,112766,107811,112112,116068,32974,32007,96296,115278,115817,112764,77234,99955,107154,114992,17875,110591,99884,88860,
    58302,112693,98747,91797,35978,52514,113614,76640,43256,19293,112652,115425,34462,27681,23995,26810,20143,116107,65777,109379,32836,111030
    )

query_process_sql = """
select a.*,b.item_quotation_price_num_media as item_quotation_price_num_media_product,b.cnt as cnt_product
from
(
select product_id,product_name,secondary_level_id,secondary_level_name,memory,storage,
purchase_way ,guarantee ,
avg(item_quotation_price_num) as item_quotation_price_num_avg,
min(item_quotation_price_num) as item_quotation_price_num_min,
max(item_quotation_price_num) as item_quotation_price_num_max,
approx_percentile(item_quotation_price_num,0.5) item_quotation_price_num_media,
count(1) as cnt
from
(
SELECT 
cdp.document_item_id,
cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
 cast(psdp.settle_document_create_date as date) as settle_list_create_date,
cast(cdp.create_date as date) AS shop_out_date,
cast(dp.product_id as int) product_id,
trim(dp.product_name) as product_name,
cast(dp.product_brand_id  as int) as brand_id,
trim(dp.product_brand_name) as product_brand_name,
cast(cdp.product_combine_level_id  as int) as level_id,
trim(cdp.product_combine_level_name) AS product_level_name,
sku2level.secondary_level_id,
level.product_level_name  AS secondary_level_name,
cdp.product_no, 
dpk.memory_name as memory ,
dpk.memory_name as storage , 
dpk.color_name as body_color ,
dpk.small_model_name as small_version ,
dpk.purchase_channel_name as purchase_way ,
dpk.network_standard_name as guarantee ,
dpk.network_standard_name as networks

FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_mobile cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 1 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id !=52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}
)a
group by product_id,product_name,secondary_level_id,secondary_level_name,memory,storage,
purchase_way ,guarantee 
)a
left join  (SELECT dp.product_id,dpk.memory_name as storage,approx_percentile(psdp.settle_amount_num,0.5) item_quotation_price_num_media,
count(1) as cnt
FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_mobile cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 1 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id !=52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}
group by 1,2) b
on a.product_id=b.product_id and a.storage=b.storage

"""
# where a.product_id in (25827)
#获取型号 等级，ppv组合的售卖量信息
query_ppv_combine_sql="""

select 
product_id,product_name,secondary_level_id,
storage,purchase_way,guarantee,count(1) as cnt
from
(
SELECT 
cdp.document_item_id,
cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
 cast(psdp.settle_document_create_date as date) as settle_list_create_date,
cast(cdp.create_date as date) AS shop_out_date,
cast(dp.product_id as int) product_id,
trim(dp.product_name) as product_name,
cast(dp.product_brand_id  as int) as brand_id,
trim(dp.product_brand_name) as product_brand_name,
cast(cdp.product_combine_level_id  as int) as level_id,
trim(cdp.product_combine_level_name) AS product_level_name,
sku2level.secondary_level_id,
level.product_level_name  AS secondary_level_name,
cdp.product_no, 
dpk.memory_name as memory ,
dpk.memory_name as storage , 
dpk.color_name as body_color ,
dpk.small_model_name as small_version ,
dpk.purchase_channel_name as purchase_way ,
dpk.network_standard_name as guarantee ,
dpk.network_standard_name as networks

FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_mobile cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 1 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id !=52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}
  )a
group by 1,2,3,4,5,6

"""
#获取ppv打分和rank数据
query_ppv_rank_sql = """
  select distinct column_name,column_value_name,ppv_rank, ppv_rank as low_ppv_rank from algo.algo_c2b_ppv_rank_android_extend
 """

#获取型号维度的ppv组合的出货数量
query_product_ppv_cnt="""
select a.*,b.item_quotation_price_num_media as item_quotation_price_num_media_product,b.cnt as cnt_product
from
(
select product_id,product_name,storage,
purchase_way ,guarantee ,
avg(item_quotation_price_num) as item_quotation_price_num_avg,
min(item_quotation_price_num) as item_quotation_price_num_min,
max(item_quotation_price_num) as item_quotation_price_num_max,
approx_percentile(item_quotation_price_num,0.5) item_quotation_price_num_media,
count(1) as cnt
from
(
SELECT 
cdp.document_item_id,
cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
 cast(psdp.settle_document_create_date as date) as settle_list_create_date,
cast(cdp.create_date as date) AS shop_out_date,
cast(dp.product_id as int) product_id,
trim(dp.product_name) as product_name,
cast(dp.product_brand_id  as int) as brand_id,
trim(dp.product_brand_name) as product_brand_name,
cast(cdp.product_combine_level_id  as int) as level_id,
trim(cdp.product_combine_level_name) AS product_level_name,
sku2level.secondary_level_id,
level.product_level_name  AS secondary_level_name,
cdp.product_no, 
dpk.memory_name as memory ,
dpk.memory_name as storage , 
dpk.color_name as body_color ,
dpk.small_model_name as small_version ,
dpk.purchase_channel_name as purchase_way ,
dpk.network_standard_name as guarantee ,
dpk.network_standard_name as networks

FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_mobile cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 1 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id !=52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}

)a
group by product_id,product_name,storage,
purchase_way ,guarantee 
)a
left join (SELECT dp.product_id,dpk.memory_name as storage,approx_percentile(psdp.settle_amount_num,0.5) item_quotation_price_num_media,
count(1) as cnt
FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_mobile cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 1 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id !=52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}
group by 1,2) b
on a.product_id=b.product_id and a.storage=b.storage

"""
# and dpk.purchase_channel_name ='大陆国行（苹果系统型号M开头）'

#sku+ppv组合的售卖量
query_sku_sale_cnt="""
select product_id,product_name,product_sku_id,storage,
purchase_way ,guarantee ,
count(1) as cnt
from
(
SELECT 
cdp.document_item_id,
cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
 cast(psdp.settle_document_create_date as date) as settle_list_create_date,
cast(cdp.create_date as date) AS shop_out_date,
cast(dp.product_id as int) product_id,
trim(dp.product_name) as product_name,
  dpk.product_sku_id,
cast(dp.product_brand_id  as int) as brand_id,
trim(dp.product_brand_name) as product_brand_name,
cast(cdp.product_combine_level_id  as int) as level_id,
trim(cdp.product_combine_level_name) AS product_level_name,
sku2level.secondary_level_id,
level.product_level_name  AS secondary_level_name,
  substring(level.product_level_name,1,1) as level_sub,
cdp.product_no, 
dpk.memory_name as memory ,
dpk.memory_name as storage , 
dpk.color_name as body_color ,
dpk.small_model_name as small_version ,
dpk.purchase_channel_name as purchase_way ,
dpk.network_standard_name as guarantee ,
dpk.network_standard_name as networks

FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_mobile cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 1 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id !=52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}
)a
group by product_id,product_name,product_sku_id,storage,
purchase_way ,guarantee 
"""

query_level_sub_ppv_sql="""
select product_id,product_name,level_sub,storage,
purchase_way ,guarantee ,
avg(item_quotation_price_num) as level_item_quotation_avg_price ,
count(1) as level_cnt_product
from
(
SELECT 
cdp.document_item_id,
cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
 cast(psdp.settle_document_create_date as date) as settle_list_create_date,
cast(cdp.create_date as date) AS shop_out_date,
cast(dp.product_id as int) product_id,
trim(dp.product_name) as product_name,
cast(dp.product_brand_id  as int) as brand_id,
trim(dp.product_brand_name) as product_brand_name,
cast(cdp.product_combine_level_id  as int) as level_id,
trim(cdp.product_combine_level_name) AS product_level_name,
sku2level.secondary_level_id,
level.product_level_name  AS secondary_level_name,
  substring(level.product_level_name,1,1) as level_sub,
cdp.product_no, 
dpk.memory_name as memory ,
dpk.memory_name as storage , 
dpk.color_name as body_color ,
dpk.small_model_name as small_version ,
dpk.purchase_channel_name as purchase_way ,
dpk.network_standard_name as guarantee ,
dpk.network_standard_name as networks

FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_mobile cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 1 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id !=52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}
)a
group by product_id,product_name,level_sub,storage,
purchase_way ,guarantee 

"""

query_ppv_combine_all="""
select distinct dpk.product_id,dpk.product_name,
dpk.memory_name as storage , 
dpk.purchase_channel_name as purchase_way ,
dpk.network_standard_name as guarantee 
FROM  dim.dim_product_sku dpk 
JOIN dim.dim_product_sku_channel_mapping dps 
ON dpk.product_sku_id = dps.mapping_product_sku_id 
where dpk.product_category_id=1 and dpk.product_brand_id !=52  and dpk.is_product_sku_active_flag=1 and dpk.business_channel='0'
      and dpk.if_c2b_product_flag=1
      and dpk.product_id in {}
order by 1 desc,dpk.memory_name,dpk.purchase_channel_name,dpk.network_standard_name
""".format(andoriod_product_ids)


query_product_ppv_small_version_cnt="""
select a.*,b.item_quotation_price_num_media as item_quotation_price_num_media_product,b.cnt as cnt_product
from
(
select product_id,product_name,storage,
purchase_way ,guarantee ,small_version,
avg(item_quotation_price_num) as item_quotation_price_num_avg,
min(item_quotation_price_num) as item_quotation_price_num_min,
max(item_quotation_price_num) as item_quotation_price_num_max,
approx_percentile(item_quotation_price_num,0.5) item_quotation_price_num_media,
count(1) as cnt
from
(
SELECT 
cdp.document_item_id,
cast(psdp.settle_amount_num as int)AS item_quotation_price_num,
 cast(psdp.settle_document_create_date as date) as settle_list_create_date,
cast(cdp.create_date as date) AS shop_out_date,
cast(dp.product_id as int) product_id,
trim(dp.product_name) as product_name,
cast(dp.product_brand_id  as int) as brand_id,
trim(dp.product_brand_name) as product_brand_name,
cast(cdp.product_combine_level_id  as int) as level_id,
trim(cdp.product_combine_level_name) AS product_level_name,
sku2level.secondary_level_id,
level.product_level_name  AS secondary_level_name,
cdp.product_no, 
dpk.memory_name as memory ,
dpk.memory_name as storage , 
dpk.color_name as body_color ,
dpk.warranty_duration_name as small_version ,
dpk.purchase_channel_name as purchase_way ,
dpk.network_standard_name as guarantee ,
dpk.network_standard_name as networks

FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_mobile cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 1 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id !=52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}
)a
group by product_id,product_name,storage,
purchase_way ,guarantee ,small_version
)a
left join  (SELECT dp.product_id,dpk.memory_name as storage,approx_percentile(psdp.settle_amount_num,0.5) item_quotation_price_num_media,
count(1) as cnt
FROM dw.dw_centre_document_product cdp
 JOIN dw.dw_platform_settle_document_product psdp ON psdp.product_no = cdp.product_no AND psdp.settle_document_no = cdp.document_serial_no  
inner join algo.algo_dw_source_product_no_front_sku_level_large_table sku2level on  cdp.product_no =sku2level.product_no 
JOIN dim.dim_product dp ON dp.product_id = cdp.product_id
inner join dim.dim_product_sku dpk 
on sku2level.secondary_sku_id=dpk.product_sku_id
inner join dim.dim_product_level level 
on level.product_level_id=sku2level.secondary_level_id
JOIN ods.ods_algorithm_dm_model_goods_code_property_mobile cpm  ON psdp.goods_code = cpm.goods_code
left join dw.dw_centre_document_product_out_info cdp_o on  psdp.product_no = cdp_o.product_no 
AND psdp.settle_document_no = cdp_o.document_serial_no  and cdp_o.document_create_date>=cast('{}' as date)
WHERE dp.product_category_id = 1 AND cdp.document_category_id >= 200 
AND cdp.document_item_status_id IN (201, 202, 204) 
AND psdp.recycler_id NOT IN (12599, 507, 286, 47326)
AND psdp.quotation_document_type_id = 10  
AND cdp.create_date >= cast('{}' as date) and  cdp.create_date < cast('{}' as date)
and psdp.settle_status_id not in (-1,5,9)
and substr(level.product_level_name,1,1) in ('A','B','C')
and dp.product_brand_id !=52
and psdp.product_source_id in (101,103)
and (cdp_o.sale_out_cnt is null or  cdp_o.sale_out_cnt=1)
and dp.product_id in {}
group by 1,2) b
on a.product_id=b.product_id and a.storage=b.storage

"""

BASE_LEVEL_RANK="""
SELECT 
product_level_id as secondary_level_id,product_level_name as secondary_level_name
FROM dim.dim_product_level a
WHERE product_category_id = 1 AND is_product_level_active_flag = 1
"""

FILE_DIR = 'file/'

ppv_rank_df = presto_processor.load_sql(query_ppv_rank_sql)

def get_level_rank():
    level_rank_df = presto_processor.load_sql(BASE_LEVEL_RANK)
    return level_rank_df

#获取型号 等级，ppv组合的售卖量信息
def get_ppv_combine_sql(start1_date,start2_date,end_date):
    ppv_level_df = presto_processor.load_sql(query_ppv_combine_sql.format(start1_date,start2_date,end_date,andoriod_product_ids))
    ppv_level_df['storage'] = ppv_level_df['storage'].fillna('unknown')
    ppv_level_df['purchase_way'] = ppv_level_df['purchase_way'].fillna('unknown')
    ppv_level_df['guarantee'] = ppv_level_df['guarantee'].fillna('unknown')
    ppv_level_df['ppv_combine'] = ppv_level_df['storage'] + '_' + ppv_level_df['purchase_way'] + '_' + ppv_level_df['guarantee']

    ppv_cnt_df = ppv_level_df.groupby(by=['product_id','secondary_level_id','storage'])['cnt'].agg({'total_cnt': 'sum'}).reset_index()
    ppv_level_df = ppv_level_df.merge(ppv_cnt_df,on=['product_id','secondary_level_id','storage'])
    ppv_level_df['percent']=ppv_level_df['cnt']/ppv_level_df['total_cnt'] #型号的ppv组合占总售卖量的比率
    ppv_level_df['percent_rank'] = ppv_level_df[['product_id','secondary_level_id','storage','percent']].groupby(by=['product_id','secondary_level_id','storage'])['percent'].rank(ascending=False, method='first')

    return ppv_level_df

#获取所有ppv组合的及其各个组合的得分
def get_all_combine_data_rank_score():
    combine_df = presto_processor.load_sql(query_ppv_combine_all)
    combine_df['storage'] = combine_df['storage'].fillna('unknown')
    combine_df['purchase_way'] = combine_df['purchase_way'].fillna('unknown')
    combine_df['guarantee'] = combine_df['guarantee'].fillna('unknown')
    # ppv_rank_df = presto_processor.load_sql(query_ppv_rank_sql)

    combine_df = combine_df.merge(ppv_rank_df.loc[ppv_rank_df.column_name == 'storage', ['column_value_name', 'ppv_rank', 'low_ppv_rank']],
                      how='left', left_on='storage', right_on='column_value_name')
    combine_df = combine_df.rename(columns={"ppv_rank": "storage_rank","low_ppv_rank": "storage_low_rank"})
    combine_df = combine_df.merge(ppv_rank_df.loc[ppv_rank_df.column_name == 'purchase_way', ['column_value_name', 'ppv_rank', 'low_ppv_rank']],
                      how='left', left_on='purchase_way', right_on='column_value_name')
    combine_df = combine_df.rename(columns={"ppv_rank": "purchase_way_rank","low_ppv_rank": "purchase_way_low_rank"})
    combine_df = combine_df.merge(ppv_rank_df.loc[ppv_rank_df.column_name == 'guarantee', ['column_value_name', 'ppv_rank', 'low_ppv_rank']],
                      how='left', left_on='guarantee', right_on='column_value_name')
    combine_df = combine_df.rename(columns={"ppv_rank": "guarantee_rank","low_ppv_rank": "guarantee_low_rank"})

    combine_df['storage_rank']=combine_df['storage_rank'].fillna(100)
    combine_df['storage_low_rank']=combine_df['storage_low_rank'].fillna(100)
    combine_df['purchase_way_rank']=combine_df['purchase_way_rank'].fillna(100)
    combine_df['purchase_way_low_rank']=combine_df['purchase_way_low_rank'].fillna(100)
    combine_df['guarantee_rank']=combine_df['guarantee_rank'].fillna(100)
    combine_df['guarantee_low_rank']=combine_df['guarantee_low_rank'].fillna(100)
    # combine_df['small_version_rank']=combine_df['small_version_rank'].fillna(100)
    # combine_df['small_version_rank_rate'] = combine_df['small_version_rank']*1.00000/100
    combine_df['rank_score'] = combine_df['storage_rank'] * combine_df['purchase_way_rank'] * combine_df['guarantee_rank']
    combine_df['rank_low_score'] = combine_df['storage_low_rank'] * combine_df['purchase_way_low_rank'] * combine_df['guarantee_low_rank']
    combine_df.loc[combine_df.product_id.isin([27640,27639,27637,25827,25680,25679,23423,23422,20079,17752,17726,17462,17461]), 'rank_score'] = combine_df.loc[combine_df.product_id.isin([27640,27639,27637,25827,25680,25679,23423,23422,20079,17752,17726,17462,17461]), 'rank_low_score']

    combine_df['ppv_combine'] = combine_df['storage'] + '_' + combine_df['purchase_way'] + '_' + combine_df['guarantee']

    return  combine_df

# 获取ppv的rank score数据
def get_product_base_ppv_fun(combine_df,start1_date,start2_date,end_date):
    df = presto_processor.load_sql(query_product_ppv_cnt.format(start1_date,start2_date,end_date,andoriod_product_ids,start1_date,start2_date,end_date,andoriod_product_ids))
    #聚合ppv
    df['storage']=df['storage'].fillna('unknown')
    df['purchase_way']=df['purchase_way'].fillna('unknown')
    df['guarantee']=df['guarantee'].fillna('unknown')

    df['ppv_combine'] = df['storage'] + '_' + df['purchase_way'] + '_' + df['guarantee']
    df['ppv_base_rank'] = df[['product_id','storage','purchase_way','guarantee','ppv_combine','cnt']].groupby(by=['product_id','storage'])['cnt'].rank(ascending=False, method='first')

    # 获取ppv的rank score数据
    df = df.merge(combine_df[['product_id','ppv_combine','rank_score','rank_low_score']],on=['product_id','ppv_combine'])
    df =df.rename(columns={"rank_score": "rank_score_base","rank_low_score": "rank_low_score_base"})

    ret_df = df.loc[df.ppv_base_rank==1,['product_id','ppv_combine','storage','cnt','ppv_base_rank','rank_score_base','rank_low_score_base']] #确保有竟得的组合中留下那些出货量最多的组合
    return ret_df

def query_level_sub_ppv_price(start1_date,start2_date,end_date):
    df = presto_processor.load_sql(query_level_sub_ppv_sql.format(start1_date,start2_date,end_date,andoriod_product_ids))
    df['storage']=df['storage'].fillna('unknown')
    df['purchase_way']=df['purchase_way'].fillna('unknown')
    df['guarantee']=df['guarantee'].fillna('unknown')

    df['ppv_combine'] = df['storage'] + '_' + df['purchase_way'] + '_' + df['guarantee']
    return df